using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using PropertyExpression.Common;
using PropertyExpression.ControllableQuery;

namespace PropertyExpression.ControllableQueryTool
{
    public static class QueryChecker
    {
        public static void CheckQueries<T>(params Func<CheckQueryContext, IQuery<T>>[] queryFuncs)
        {
            Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
            foreach (var queryFunc in queryFuncs)
            {
                var paramInfoList = new List<ParamInfo>();
                var queryText = queryFunc(new CheckQueryContext(paramInfoList)).TransformText();

                var executionResult = WithSqlCommand(
                    sqlCommand =>
                        {
                            sqlCommand.CommandText = "SET SHOWPLAN_XML ON";
                            sqlCommand.ExecuteNonQuery();

                            var dictionary = new Dictionary<string, string>();
                            var result = ExecuteQuery(sqlCommand, queryText, paramInfoList, dictionary);

                            sqlCommand.CommandText = "SET SHOWPLAN_XML OFF";
                            sqlCommand.ExecuteNonQuery();

                            return new {result, dictionary};
                        }
                    );

                var xmlDocument = new XmlDocument();
                xmlDocument.LoadXml((string) executionResult.result);
                var xmlNamespaceManager = new XmlNamespaceManager(xmlDocument.NameTable);
                xmlNamespaceManager.AddNamespace("a", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");

                //TODO:
                Console.WriteLine(queryText);
                Console.WriteLine("#################");

                var sqlParamInfoList = paramInfoList.Select(
                    paramInfo =>
                    new
                        {
                            paramInfo,
                            SqlType =
                        GetSqlType(executionResult.dictionary, paramInfo.Name).Process(
                            value => value,
                            () => paramInfo.SqlTypeFunc(paramInfo, xmlDocument, xmlNamespaceManager))
                        }
                    );

                foreach (var sqlParamInfo in sqlParamInfoList)
                {
                    Console.WriteLine("{0} {1} {2}",
                                      sqlParamInfo.paramInfo.Name,
                                      sqlParamInfo.paramInfo.ParamType,
                                      sqlParamInfo.SqlType);
                }

                Console.WriteLine("****************************");


                WithSqlCommand(
                    sqlCommand =>
                        {
                            sqlCommand.CommandText = GetCommandText(
                                sqlParamInfoList.Select(
                                    sqlParamInfo => Tuple.Create(sqlParamInfo.paramInfo.Name, sqlParamInfo.SqlType)
                                    ),
                                queryText);

                            DataTable schemaTable;
                            using (var sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly))
                            {
                                schemaTable = sqlDataReader.GetSchemaTable();
                            }
                            foreach (DataRow dataRow in schemaTable.Rows)
                            {
                                Console.WriteLine(@"{0} {1} '{2}'",
                                                  dataRow["ColumnName"],
                                                  dataRow["DataType"],
                                                  dataRow["AllowDBNull"]);
                            }
                        }
                    );

                Console.WriteLine("------------------------------");

                foreach (var propertyInfo in typeof (T).GetProperties())
                {
                    Console.WriteLine("{0} {1}", propertyInfo.Name, propertyInfo.PropertyType);
                }

                Console.WriteLine("****************************");
            }
        }

        private static IOptionalValue<string> GetSqlType(Dictionary<string, string> dictionary, string name)
        {
            string value;
            return dictionary.TryGetValue(name, out value) ? value.AsOptionalValue() : OptionalValue.Nothing<string>();
        }

        private static object ExecuteQuery(
            SqlCommand sqlCommand, string queryText, IEnumerable<ParamInfo> paramInfoList, Dictionary<string, string> dictionary)
        {
            var commandText = GetCommandText(
                paramInfoList.Select(
                    paramInfo => Tuple.Create(
                        paramInfo.Name,
                        paramInfo.SqlDeclareInfoFunc(paramInfo).Process(
                            value => value,
                            () => GetSqlType(dictionary, paramInfo.Name).Process(value => value, () => "VARBINARY(MAX)")
                            )
                                     )
                    ),
                queryText);
            sqlCommand.CommandText = commandText;
            try
            {
                return sqlCommand.ExecuteScalar();
            }
            catch (SqlException e)
            {
                if (e.Number == 1060)
                {
                    dictionary.Add(ToLines(commandText).ToArray()[e.LineNumber - 1], "bigint");
                    return ExecuteQuery(sqlCommand, queryText, paramInfoList, dictionary);
                }
                else
                {
                    throw;
                }
            }
        }

        private static IEnumerable<string> ToLines(string queryText)
        {
            using (var reader = new StringReader(queryText))
            {
                string line;
                while ((line = reader.ReadLine()) != null)
                    yield return line;
            }
        }

        private static string GetCommandText(IEnumerable<Tuple<string, string>> paramInfoList, string queryText)
        {
            var paramStringBuilder = new StringBuilder();
            foreach (var paramInfo in paramInfoList)
            {
                paramStringBuilder.AppendFormat("DECLARE {0} {1}", paramInfo.Item1, paramInfo.Item2);
                paramStringBuilder.AppendLine();
            }
            return string.Format(@"{0}
{1}",
                                 paramStringBuilder,
                                 queryText);
        }

        private static TResult WithSqlCommand<TResult>(Func<SqlCommand, TResult> func)
        {
            using (var connection = new SqlConnection(
                @"Data Source=ws026\MSSQLSERVER2008;Initial Catalog=ControllableQuery;Integrated Security=True"))
            {
                connection.Open();

                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = connection;

                    return func(sqlCommand);
                }
            }
        }

        private static void WithSqlCommand(Action<SqlCommand> action)
        {
            WithSqlCommand(
                command =>
                    {
                        action(command);
                        return new {};
                    }
                );
        }
    }

    public static class Tuple
    {
        public static Tuple<T1, T2> Create<T1, T2>(T1 item1, T2 item2)
        {
            return new Tuple<T1, T2>(item1, item2);
        }
    }

    public class Tuple<T1, T2>
    {
        private readonly T1 item1;
        private readonly T2 item2;

        public Tuple(T1 item1, T2 item2)
        {
            this.item1 = item1;
            this.item2 = item2;
        }

        public T2 Item2
        {
            get { return item2; }
        }

        public T1 Item1
        {
            get { return item1; }
        }
    }
}